<!DOCTYPE html><html lang="zh-Hans"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"><meta name="description" content="Mysql高性能——索引篇"><meta name="keywords" content="MySQL"><meta name="author" content="qqggzwm"><meta name="copyright" content="qqggzwm"><title>Mysql高性能——索引篇 | qqggzwmの博客</title><link rel="shortcut icon" href="/blog/melody-favicon.ico"><link rel="stylesheet" href="/blog/css/index.css?version=1.9.0"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/font-awesome@latest/css/font-awesome.min.css?version=1.9.0"><meta name="format-detection" content="telephone=no"><meta http-equiv="x-dns-prefetch-control" content="on"><link rel="dns-prefetch" href="https://cdn.jsdelivr.net"><meta http-equiv="Cache-Control" content="no-transform"><meta http-equiv="Cache-Control" content="no-siteapp"><script>var GLOBAL_CONFIG = { 
  root: '/blog/',
  algolia: undefined,
  localSearch: undefined,
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  hexoVersion: '5.3.0'
} </script><meta name="generator" content="Hexo 5.3.0"><link rel="alternate" href="/blog/atom.xml" title="qqggzwmの博客" type="application/atom+xml">
</head><body><i class="fa fa-arrow-right" id="toggle-sidebar" aria-hidden="true"></i><div id="sidebar" data-display="true"><div class="toggle-sidebar-info text-center"><span data-toggle="切换文章详情">切换站点概览</span><hr></div><div class="sidebar-toc"><div class="sidebar-toc__title">目录</div><div class="sidebar-toc__progress"><span class="progress-notice">你已经读了</span><span class="progress-num">0</span><span class="progress-percentage">%</span><div class="sidebar-toc__progress-bar"></div></div><div class="sidebar-toc__content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#MySQL%E9%AB%98%E6%80%A7%E8%83%BD%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95%E7%AF%87"><span class="toc-number">1.</span> <span class="toc-text">MySQL高性能——索引篇</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E7%B1%BB%E5%9E%8B"><span class="toc-number">1.1.</span> <span class="toc-text">索引类型</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#B-Tree%E7%B4%A2%E5%BC%95"><span class="toc-number">1.1.1.</span> <span class="toc-text">B-Tree索引</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E9%80%82%E7%94%A8%E5%9C%BA%E6%99%AF"><span class="toc-number">1.1.1.1.</span> <span class="toc-text">适用场景*</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#%E5%85%A8%E5%80%BC%E5%8C%B9%E9%85%8D"><span class="toc-number">1.1.1.1.1.</span> <span class="toc-text">全值匹配</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E5%8C%B9%E9%85%8D%E6%9C%80%E5%B7%A6%E5%89%8D%E7%BC%80"><span class="toc-number">1.1.1.1.2.</span> <span class="toc-text">匹配最左前缀</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E5%8C%B9%E9%85%8D%E5%88%97%E5%89%8D%E7%BC%80"><span class="toc-number">1.1.1.1.3.</span> <span class="toc-text">匹配列前缀</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E5%8C%B9%E9%85%8D%E8%8C%83%E5%9B%B4%E5%80%BC"><span class="toc-number">1.1.1.1.4.</span> <span class="toc-text">匹配范围值</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#%E7%B2%BE%E7%A1%AE%E5%8C%B9%E9%85%8D%E6%9F%90%E4%B8%80%E5%88%97%EF%BC%8C%E8%8C%83%E5%9B%B4%E5%8C%B9%E9%85%8D%E5%8F%A6%E4%B8%80%E5%88%97"><span class="toc-number">1.1.1.1.5.</span> <span class="toc-text">精确匹配某一列，范围匹配另一列</span></a></li></ol></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%93%88%E5%B8%8C%E7%B4%A2%E5%BC%95"><span class="toc-number">1.1.2.</span> <span class="toc-text">哈希索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%85%A8%E6%96%87%E7%B4%A2%E5%BC%95"><span class="toc-number">1.1.3.</span> <span class="toc-text">全文索引</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E4%BC%98%E7%82%B9"><span class="toc-number">1.2.</span> <span class="toc-text">索引优点</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E9%AB%98%E6%80%A7%E8%83%BD%E7%B4%A2%E5%BC%95%E7%AD%96%E7%95%A5"><span class="toc-number">1.3.</span> <span class="toc-text">高性能索引策略</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%8B%AC%E7%AB%8B%E7%9A%84%E5%88%97"><span class="toc-number">1.3.1.</span> <span class="toc-text">独立的列</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%89%8D%E7%BC%80%E7%B4%A2%E5%BC%95%E5%92%8C%E7%B4%A2%E5%BC%95%E9%80%89%E6%8B%A9%E6%80%A7"><span class="toc-number">1.3.2.</span> <span class="toc-text">前缀索引和索引选择性</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%9A%E5%88%97%E7%B4%A2%E5%BC%95"><span class="toc-number">1.3.3.</span> <span class="toc-text">多列索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E9%80%89%E6%8B%A9%E5%90%88%E9%80%82%E7%9A%84%E7%B4%A2%E5%BC%95%E9%A1%BA%E5%BA%8F"><span class="toc-number">1.3.4.</span> <span class="toc-text">选择合适的索引顺序</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95or%E9%9D%9E%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95%EF%BC%88%E4%BA%8C%E7%BA%A7%E7%B4%A2%E5%BC%95%EF%BC%89"><span class="toc-number">1.3.5.</span> <span class="toc-text">聚簇索引or非聚簇索引（二级索引）</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BB%80%E4%B9%88%E6%98%AF%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95"><span class="toc-number">1.3.5.1.</span> <span class="toc-text">什么是聚簇索引</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95%E5%92%8C%E9%9D%9E%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95%E7%9A%84%E5%8C%BA%E5%88%AB"><span class="toc-number">1.3.5.2.</span> <span class="toc-text">聚簇索引和非聚簇索引的区别</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#%E4%BE%8B%E5%AD%90"><span class="toc-number">1.3.5.2.1.</span> <span class="toc-text">例子</span></a></li></ol></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A6%86%E7%9B%96%E7%B4%A2%E5%BC%95"><span class="toc-number">1.3.6.</span> <span class="toc-text">覆盖索引</span></a></li></ol></li></ol></li></ol></div></div><div class="author-info hide"><div class="author-info__avatar text-center"><img src="https://gitee.com/qqggzwm/blog-img/raw/master/img/20210120225331.jpg"></div><div class="author-info__name text-center">qqggzwm</div><div class="author-info__description text-center"></div><hr><div class="author-info-articles"><a class="author-info-articles__archives article-meta" href="/blog/archives"><span class="pull-left">文章</span><span class="pull-right">5</span></a><a class="author-info-articles__tags article-meta" href="/blog/tags"><span class="pull-left">标签</span><span class="pull-right">5</span></a><a class="author-info-articles__categories article-meta" href="/blog/categories"><span class="pull-left">分类</span><span class="pull-right">3</span></a></div></div></div><div id="content-outer"><div class="no-bg" id="top-container"><div id="page-header"><span class="pull-left"> <a id="site-name" href="/blog/">qqggzwmの博客</a></span><i class="fa fa-bars toggle-menu pull-right" aria-hidden="true"></i><span class="pull-right menus">   <a class="site-page" href="/">Home</a><a class="site-page" href="/blog/archives">Archives</a><a class="site-page" href="/blog/tags">Tags</a><a class="site-page" href="/blog/categories">Categories</a></span><span class="pull-right"></span></div><div id="post-info"><div id="post-title">Mysql高性能——索引篇</div><div id="post-meta"><time class="post-meta__date"><i class="fa fa-calendar" aria-hidden="true"></i> 2021-03-23</time></div></div></div><div class="layout" id="content-inner"><article id="post"><div class="article-container" id="post-content"><h1 id="MySQL高性能——索引篇"><a href="#MySQL高性能——索引篇" class="headerlink" title="MySQL高性能——索引篇"></a>MySQL高性能——索引篇</h1><p>本章节经常用到 explain 语句查看 sql 运行信息，附上相关链接查看对于属性</p>
<p><a target="_blank" rel="noopener" href="https://blog.csdn.net/jiadajing267/article/details/81269067">https://blog.csdn.net/jiadajing267/article/details/81269067</a> </p>
<h2 id="索引类型"><a href="#索引类型" class="headerlink" title="索引类型"></a>索引类型</h2><h3 id="B-Tree索引"><a href="#B-Tree索引" class="headerlink" title="B-Tree索引"></a>B-Tree索引</h3><p>按顺序存储</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322123353.png" alt="image-20201121152525264"></p>
<p>B-Tree 索引可以加快访问数据的速度原因是：不需要全表扫描，只需要从索引的根节点开始搜索即可，我们知道树的搜索时间复杂度是 O(log2(n))</p>
<p>使用 B+ 树而不使用红黑树的原因？</p>
<blockquote>
<p>B 树是多路树，红黑树是二叉树！红黑树一个节点只能存出一个值，B 树一个节点可以存储多个值，红黑树的深度会更大,定位时 红黑树的查找次数会大一些。更浅的树高说明搜索的次数更少， 磁盘 I/O 的次数更少</p>
</blockquote>
<h4 id="适用场景"><a href="#适用场景" class="headerlink" title="适用场景*"></a>适用场景*</h4><p>由于树的存储方式是按照顺序存储的，所以 B-Tree 索引适用于 ==范围搜索==（查找 I 到 K 开头的字母）</p>
<p>可以使用 B-Tree 索引查询类型：全键值、键值范围、键前缀查找</p>
<p>现有表 people，表有复合索引key(last_name,first_name,dob)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> people;</span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+------------+------------+--------+</span></span><br><span class="line"><span class="operator">|</span> last_name <span class="operator">|</span> first_name <span class="operator">|</span> dob        <span class="operator">|</span> gender <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+------------+------------+--------+</span></span><br><span class="line"><span class="operator">|</span> allen     <span class="operator">|</span> cuba       <span class="operator">|</span> <span class="number">2021</span><span class="number">-03</span><span class="number">-22</span> <span class="operator">|</span> <span class="keyword">NULL</span>   <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> astralre  <span class="operator">|</span> angellna   <span class="operator">|</span> <span class="number">2021</span><span class="number">-02</span><span class="number">-05</span> <span class="operator">|</span> <span class="keyword">NULL</span>   <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> akroyd    <span class="operator">|</span> christian  <span class="operator">|</span> <span class="number">2020</span><span class="number">-12</span><span class="number">-16</span> <span class="operator">|</span> <span class="keyword">NULL</span>   <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> akoyrd    <span class="operator">|</span> klrsten    <span class="operator">|</span> <span class="number">1978</span><span class="number">-11</span><span class="number">-02</span> <span class="operator">|</span> <span class="keyword">NULL</span>   <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> barrymore <span class="operator">|</span> julia      <span class="operator">|</span> <span class="number">2000</span><span class="number">-05</span><span class="number">-16</span> <span class="operator">|</span> <span class="keyword">NULL</span>   <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> basinger  <span class="operator">|</span> vivien     <span class="operator">|</span> <span class="number">1979</span><span class="number">-01</span><span class="number">-24</span> <span class="operator">|</span> <span class="keyword">NULL</span>   <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> basinger  <span class="operator">|</span> viven      <span class="operator">|</span> <span class="number">1976</span><span class="number">-12</span><span class="number">-08</span> <span class="operator">|</span> <span class="keyword">NULL</span>   <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+------------+------------+--------+</span></span><br><span class="line"><span class="number">7</span> <span class="keyword">rows</span> <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br></pre></td></tr></table></figure>


<h5 id="全值匹配"><a href="#全值匹配" class="headerlink" title="全值匹配"></a>全值匹配</h5><p>​        查询条件全匹配索引，查询lastname 为 allen，firstname为cuba 并且出生日期在2021-03-22的。这三条件字段都覆盖了索引的列</p>
<h5 id="匹配最左前缀"><a href="#匹配最左前缀" class="headerlink" title="匹配最左前缀"></a>匹配最左前缀</h5><p>​        查询姓为 allen 的人，这里只用到了索引的第一列</p>
<h5 id="匹配列前缀"><a href="#匹配列前缀" class="headerlink" title="匹配列前缀"></a>匹配列前缀</h5><blockquote>
<p>4.【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。 说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。</p>
<p>参考阿里巴巴开发手册嵩山版2020.8 第五章 (二) 索引规约</p>
</blockquote>
<p>​        </p>
<p>​            匹配列前缀可用到索引 like xxx%，like %xxx%、like %xxx 大部分情况用不到索引(覆盖索引除外)；</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322141029.png" alt="image-20210322141029784"></p>
<p>ps：不能使用select * 否则无法使用索引，这样将无法覆盖索引</p>
<h5 id="匹配范围值"><a href="#匹配范围值" class="headerlink" title="匹配范围值"></a>匹配范围值</h5><p>​        查找姓在 allen 和 basinger 之间的人</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322142532.png" alt="image-20210322142532936"></p>
<h5 id="精确匹配某一列，范围匹配另一列"><a href="#精确匹配某一列，范围匹配另一列" class="headerlink" title="精确匹配某一列，范围匹配另一列"></a>精确匹配某一列，范围匹配另一列</h5><p>​        即第一列全匹配，第二列范围匹配</p>
<p>​        查找姓氏为 allen 名字是 k开头或者k结尾，或者名字中带k。</p>
<p>​        经过分析都达到了 ref 的级别</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322160118.png" alt="image-20210322160118879"></p>
<p>索引失效的场景</p>
<p><strong>不是按照索引最左列匹配</strong></p>
<p>如果要查名字是 switch 出生日期是 2021-3-22，由于这两列都不是索引的最左两列，因此无法使用索引</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322162931.png" alt="image-20210322162931013"></p>
<p><strong>不能跳过索引</strong></p>
<p>如果想查询 last_name 姓名为 allen 日期在 2021-3-22 出生的人，如果不指定 first_name 只能用索引的第一列</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322161421.png"></p>
<p><strong>如果其中一列时范围搜索那么，后面的列将无法使用索引</strong></p>
<p>last_name 是 allen ，名字是 k %，出生日期是2021-3-22</p>
<p>由于姓名列是范围搜索，会导致后面出生日期列无法使用索引优化</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322162544.png" alt="image-20210322162544185"></p>
<p>总结：遵循最左匹配原则，避免非最左列出现范围匹配，如between，&gt;，&lt;，like %</p>
<h3 id="哈希索引"><a href="#哈希索引" class="headerlink" title="哈希索引"></a>哈希索引</h3><h3 id="全文索引"><a href="#全文索引" class="headerlink" title="全文索引"></a>全文索引</h3><h2 id="索引优点"><a href="#索引优点" class="headerlink" title="索引优点"></a>索引优点</h2><ol>
<li>减少数据扫描的次数，避免全表搜索</li>
<li>帮助服务器避免排序和零时表</li>
<li>索引可以将随机 I/O 变为 顺序 I/O</li>
</ol>
<p>索引是最好的解决方案吗？</p>
<p>小型表：全表扫描更加高效</p>
<p>中型到大型表：索引很有效</p>
<p>特大型表：创建和使用维护索引的代价高，采用<strong>分区技术</strong></p>
<p>TB级别的数据：<strong>块级元数据</strong></p>
<p>知识盲区：元数据、块级元数据</p>
<h2 id="高性能索引策略"><a href="#高性能索引策略" class="headerlink" title="高性能索引策略"></a>高性能索引策略</h2><h3 id="独立的列"><a href="#独立的列" class="headerlink" title="独立的列"></a>独立的列</h3><p>索引列不能是表达式或者函数的参数</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322175629.png" alt="image-20210322175629848"></p>
<h3 id="前缀索引和索引选择性"><a href="#前缀索引和索引选择性" class="headerlink" title="前缀索引和索引选择性"></a>前缀索引和索引选择性</h3><blockquote>
<p>【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度。Java 开发手册 40/59 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。</p>
<p>——参考 阿里巴巴开发手册嵩山版2020.8 第五章MySQL 数据库(二) 索引规约 </p>
</blockquote>
<p>如果列是字符串列，则索引将占用大量磁盘空间并可能减慢<code>INSERT</code>操作速度。</p>
<p>为解决此问题，MySQL允许您使用以下语法为字符串列的列值的前导部分创建索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">column_name(length) </span><br></pre></td></tr></table></figure>
<p>例如，以下语句在创建表时创建列前缀键部分：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> table_name(</span><br><span class="line">    column_list,</span><br><span class="line">    INDEX(column_name(length))</span><br><span class="line">); </span><br></pre></td></tr></table></figure>
<p>或者向现有表添加索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> INDEX index_name</span><br><span class="line"><span class="keyword">ON</span> table_name(column_name(length)); </span><br></pre></td></tr></table></figure>


<p>索引的选择性是 ：不重复的索引值和数据表总数的比值 COUNT(DISCTINCT city) / COUNT(*)</p>
<p>选择性高的索引可以查找的时候过滤更多的列，唯一索引的选择性是1。</p>
<p>例子：现在有很多名字或杂合文本，并且他们的长度都非常的长，mysql 是不允许索引这些列的完整长度的（BLOB、TEXT或者很长的VARCHAR）。</p>
<p>那我们就需要得到长度为多少的索引才能把他们很好的区分开呢。太长占用空间，太短区分度不大</p>
<p>如果索引长度为 1 ，那么很多的城市都是 a头的，区分度不高</p>
<p>如果索引长度为20，那么和直接去查好像没有什么区别，并且如果索引到了一定的程度，选择性就提升不明显。</p>
<p>那么如何去选择最佳的长度呢？</p>
<p>首先获取全部数据</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322183417.png" alt="image-20210322183417607"></p>
<p>现在表有这些数据</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322184133.png" alt="image-20210322184133769"></p>
<p>根据公式去计算出索引的选择性，越高越好。当我们选择前10个字符为作为索引的长度区分度很高，但是当索引长度超过了 10 选择性提升就不明显了。所以创建索引长度为 10 的索引为最佳的选择。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#为现有表添加索引</span><br><span class="line"><span class="keyword">CREATE</span> INDEX preindex</span><br><span class="line"><span class="keyword">ON</span> city(name(<span class="number">10</span>)) </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210322185053.png" alt="有索引和无索引对比"></p>
<h3 id="多列索引"><a href="#多列索引" class="headerlink" title="多列索引"></a>多列索引</h3><p>把多个单列索引合并成一个多列索引，Mysql 优化器会自动把我们优化——索引合并，但是这样容易造成资源的浪费。</p>
<h3 id="选择合适的索引顺序"><a href="#选择合适的索引顺序" class="headerlink" title="选择合适的索引顺序"></a>选择合适的索引顺序</h3><blockquote>
<ol start="9">
<li><p>【推荐】建组合索引的时候，区分度最高的在最左边。</p>
<p>正例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。 </p>
</li>
</ol>
<p>   ==说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c&gt;? and d=?  那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。==</p>
<p>——阿里巴巴开发规范嵩山版2020.8 MySQL数据库</p>
</blockquote>
<p>一般把选择性高的索引放在前面</p>
<h3 id="聚簇索引or非聚簇索引（二级索引）"><a href="#聚簇索引or非聚簇索引（二级索引）" class="headerlink" title="聚簇索引or非聚簇索引（二级索引）"></a>聚簇索引or非聚簇索引（二级索引）</h3><p>聚簇索引：聚集索引，主键索引</p>
<p>非聚簇索：辅助索引、普通索引</p>
<h4 id="什么是聚簇索引"><a href="#什么是聚簇索引" class="headerlink" title="什么是聚簇索引"></a>什么是聚簇索引</h4><p>聚簇索引是一种数据存储方式</p>
<p>只有InnoDB采用这种方式存储，其他的引擎如 MyIsam 使用的是非聚簇索引</p>
<h4 id="聚簇索引和非聚簇索引的区别"><a href="#聚簇索引和非聚簇索引的区别" class="headerlink" title="聚簇索引和非聚簇索引的区别"></a>聚簇索引和非聚簇索引的区别</h4><p>聚簇索引也叫聚集索引，它实际上并不是一种单独的索引类型，而是一种数据存储方式，聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说，聚簇索引的叶子节点中，包含了一个完整的记录行。</p>
<p>非聚簇索引也叫辅助索引、普通索引，它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为<strong>回表</strong>。</p>
<h5 id="例子"><a href="#例子" class="headerlink" title="例子"></a>例子</h5><p>例如一个包含了用户姓名和年龄的的数据表，假设主键是用户ID，聚簇索引的结构为（橙色的代表id，绿色是指向子节点的指针）：</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210327145517.png" alt="聚簇索引"></p>
<p>叶子节点中，为了突出记录，把<code>(id, name, age)</code>区分开来了，实际上是连在一起的，它们是构成一条记录的整体。</p>
<p>此时查询一条记录由于所有的叶子节点中都包含了字段和主键id，所以无需回表</p>
<p>而一个非聚簇索引（以<code>age</code>为索引）的结构是：</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210327143817.png" alt="非聚簇索引"></p>
<p>它的叶子节点中，不包含整个记录的完整信息，除了age字段本身以外，只包含当前记录的主键id。如果想要获取整行记录数据还需要再通过id号到聚簇索引中回表查询。</p>
<p>如果查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> name <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">where</span>  age <span class="operator">=</span> <span class="number">28</span>;</span><br></pre></td></tr></table></figure>
<p>此时，查询的字段 name 没有在节点信息中，需要查询到获取到主键 id 后再回表去查询</p>
<p>如果查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> age  <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">where</span> age <span class="operator">=</span> <span class="number">28</span></span><br></pre></td></tr></table></figure>
<p>此时，age 字段即在节点的信息中，无需回表操作，这个就是覆盖索引（下节会说到）</p>
<p>注意：</p>
<blockquote>
<p>主键一定是聚簇索引，MySQL的InnoDB中一定有主键，即便研发人员不手动设置，则会使用unique索引，没有unique索引，则会使用数据库内部的一个行的id来当作主键索引，其它普通索引需要区分SQL场景，当SQL查询的列就是索引本身时，我们称这种场景下该普通索引也可以叫做聚簇索引，MyIsAM引擎没有聚簇索引。</p>
</blockquote>
<p><a target="_blank" rel="noopener" href="https://cloud.tencent.com/developer/article/1631424">https://cloud.tencent.com/developer/article/1631424</a></p>
<p><a target="_blank" rel="noopener" href="https://www.dyxmq.cn/databases/mysql/clustered-nonclustered-union-and-unique-indexes-in-mysql.html">https://www.dyxmq.cn/databases/mysql/clustered-nonclustered-union-and-unique-indexes-in-mysql.html</a></p>
<p>部分内容参考引用自以上网站</p>
<h3 id="覆盖索引"><a href="#覆盖索引" class="headerlink" title="覆盖索引"></a>覆盖索引</h3><p>覆盖索引可以使数据直接在索引中找到而不需要==回表查询==，节省时间</p>
<p>由于只有 B-Tree 索引才把索引和节点信息一起存储，所有只有 B-Tree 索引可以作为覆盖索引</p>
<p>如果实现了覆盖索引的查询时，使用 EXPLAIN 的 Extra 列可以看到 Using Index 信息</p>
<p><img src="https://gitee.com/wx_9aca2fe07f/blog-img/raw/master/img/20210323233337.png" alt="image-20210323233330022"></p>
<p>覆盖索引就是要查询的字段刚好和索引字段重合</p>
<p>就好比查找某一本书某一章的标题，你只会翻开目录查找对应的标题，就不需要翻到对应页码页面去寻找。</p>
</div></article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">qqggzwm</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="http://qqggzwm.gitee.io/blog/posts/96562c9e/">http://qqggzwm.gitee.io/blog/posts/96562c9e/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc-sa/4.0/">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://qqggzwm.gitee.io/blog">qqggzwmの博客</a>！</span></div></div><div class="post-meta__tag-list"><a class="post-meta__tags" href="/blog/tags/MySQL/">MySQL</a></div><nav id="pagination"><div class="next-post pull-right"><a href="/blog/posts/7b223055/"><span>Mysql 内连接，外连接傻傻分不清楚？一文介绍清楚</span><i class="fa fa-chevron-right"></i></a></div></nav></div></div><footer><div class="layout" id="footer"><div class="copyright">&copy;2019 - 2021 By qqggzwm</div><div class="framework-info"><span>驱动 - </span><a target="_blank" rel="noopener" href="http://hexo.io"><span>Hexo</span></a><span class="footer-separator">|</span><span>主题 - </span><a target="_blank" rel="noopener" href="https://github.com/Molunerfinn/hexo-theme-melody"><span>Melody</span></a></div><div class="busuanzi"><script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><span id="busuanzi_container_page_pv"><i class="fa fa-file"></i><span id="busuanzi_value_page_pv"></span><span></span></span></div></div></footer><i class="fa fa-arrow-up" id="go-up" aria-hidden="true"></i><script src="https://cdn.jsdelivr.net/npm/animejs@latest/anime.min.js"></script><script src="https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js"></script><script src="https://cdn.jsdelivr.net/npm/velocity-animate@latest/velocity.min.js"></script><script src="https://cdn.jsdelivr.net/npm/velocity-ui-pack@latest/velocity.ui.min.js"></script><script src="/blog/js/utils.js?version=1.9.0"></script><script src="/blog/js/fancybox.js?version=1.9.0"></script><script src="/blog/js/sidebar.js?version=1.9.0"></script><script src="/blog/js/copy.js?version=1.9.0"></script><script src="/blog/js/fireworks.js?version=1.9.0"></script><script src="/blog/js/transition.js?version=1.9.0"></script><script src="/blog/js/scroll.js?version=1.9.0"></script><script src="/blog/js/head.js?version=1.9.0"></script><script>if(/Android|webOS|iPhone|iPod|iPad|BlackBerry/i.test(navigator.userAgent)) {
  $('#nav').addClass('is-mobile')
  $('footer').addClass('is-mobile')
  $('#top-container').addClass('is-mobile')
}</script></body></html>